package com.turing.post.salesmanage.impl;

import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.ejb.EJB;
import javax.ejb.Local;
import javax.ejb.Remote;
import javax.ejb.Stateless;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.turing.post.capitalaccounts.util.BankAccount;
import com.turing.post.capitalaccounts.util.BankAccountDao;
import com.turing.post.cardmanagement.util.CardClassic;
import com.turing.post.cardmanagement.util.CardClassicDao;
import com.turing.post.salesmanage.util.SaleOrder;
import com.turing.post.salesmanage.util.SaleOrderCardDetail;
import com.turing.post.salesmanage.util.SaleOrderCardDetailDao;
import com.turing.post.salesmanage.util.SaleOrderDao;
import com.turing.post.salesmanage.util.SaleOrderPaidDetail;
import com.turing.post.salesmanage.util.SaleOrderPaidDetailDao;
import com.turing.post.util.Config;
import com.turing.post.util.GeneralDao;
import com.turing.post.util.PCPSApplicationException;
import com.turing.post.util.PCPSUtil;
import com.turing.post.util.PCPSWebInput;

/**
 * @author zxp
 * @Dec 23, 2012
 */

@Stateless(mappedName = "salesmanage.impl.SaleOrderDaoImpl")
@Remote
@Local
public class SaleOrderDaoImpl implements SaleOrderDao {
	@EJB(mappedName = "salesmanage.impl.SaleOrderCardDetailDaoImpl")
	private SaleOrderCardDetailDao saleOrderCardDetailDaoImpl;// 销售单售卡明细
	@EJB(mappedName = "cardmanagement.impl.CardClassicDaoImpl")
	private CardClassicDao cardClassicDaoImpl;// 卡种类
	@EJB(mappedName = "salesmanage.impl.SaleOrderPaidDetailDaoImpl")
	private SaleOrderPaidDetailDao saleOrderPaidDetailDaoImpl;// 付款信息
	@EJB(mappedName = "capitalaccounts.impl.BankAccountDaoImpl")
	private BankAccountDao bankAccountDaoImpl;// 银行账户
	/**
	 * 实体管理器
	 * 
	 * @see javax.persistence.EntityManager
	 */
	@PersistenceContext
	protected EntityManager _em;

	@Override
	public SaleOrder getSaleOrderBySaleOrderId(BigInteger saleOrderId) {
		return _em.find(SaleOrder.class, saleOrderId);
	}

	@Override
	public SaleOrder getSaleOrderForUpdate(BigInteger saleOrderId) {
		String sql = "select * from SaleOrder t where t.SaleOrderId="
				+ saleOrderId + " for update";
		Query query = _em.createNativeQuery(sql, SaleOrder.class);
		return (SaleOrder) query.getSingleResult();
	}

	@SuppressWarnings("unchecked")
	@Override
	public SaleOrder getSaleOrderBySaleOrderSeqNo(String saleOrderSeqNo) {
		String sql = "select * from SaleOrder t where t.SaleOrderSeqNo='"
				+ saleOrderSeqNo + "'";
		Query query = _em.createNativeQuery(sql, SaleOrder.class);
		List<SaleOrder> list = query.getResultList();
		if (list == null || list.size() < 1) {
			return null;
		}
		return list.get(0);
	}

	@Override
	public void insertSaleOrder(SaleOrder saleOrder)
			throws PCPSApplicationException {
		GeneralDao.insert(_em, saleOrder);
	}

	@Override
	public void updateSaleOrder(SaleOrder saleOrder)
			throws PCPSApplicationException {
		GeneralDao.update(_em, saleOrder);
	}

	@Override
	public BigInteger getSaleOrderTotalByCondition(PCPSWebInput webInput,
			String orderSeqNo, String buyerType, String name,
			String orderStatus, BigInteger startSaleAmount,
			BigInteger endSaleAmount, String startTime, String endTime,
			String saleBranchNo, String btnStatus, String paidType,
			String feeType) {
		String sql = "select nvl(count(*),0) from SaleOrder t where (?1 is null or paidType=?1)";
		if (!PCPSUtil.isEmpty(feeType)) {
			sql += " and saleorderid in(select saleorderid from SaleOrderPaidDetail t where paidtype='"
					+ feeType
					+ "' and TranTime>=to_date('"
					+ startTime
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss') and TranTime<= to_date('"
					+ endTime + " 23:59:59','yyyy-mm-dd hh24:mi:ss'))";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		// 查询条件
		sql += getSecondSql(orderSeqNo, buyerType, name, orderStatus,
				startSaleAmount, endSaleAmount, startTime, endTime);
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			sql = getSql(btnStatus, sql);
		}
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, paidType);
		return new BigInteger(query.getSingleResult().toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleOrder> getSaleOrderListByCondition(PCPSWebInput webInput,
			String orderSeqNo, String buyerType, String name,
			String orderStatus, BigInteger startSaleAmount,
			BigInteger endSaleAmount, String startTime, String endTime,
			String saleBranchNo, String btnStatus, String paidType,
			String feeType, Integer page) {
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		// 平台管理员无法查询
		String sql = "select t.* from SaleOrder t where (?1 is null or paidType=?1)";
		if (!PCPSUtil.isEmpty(feeType)) {
			sql += " and saleorderid in(select saleorderid from SaleOrderPaidDetail t where paidtype='"
					+ feeType
					+ "' and TranTime>=to_date('"
					+ startTime
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss') and TranTime<= to_date('"
					+ endTime + " 23:59:59','yyyy-mm-dd hh24:mi:ss'))";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		// 查询条件
		sql += getSecondSql(orderSeqNo, buyerType, name, orderStatus,
				startSaleAmount, endSaleAmount, startTime, endTime);
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			sql = getSql(btnStatus, sql);
		}
		sql = sql + " order by SaleTime desc";
		Query query = null;
		if (page.intValue() == -1) {
			query = _em.createNativeQuery(sql, SaleOrder.class);
		} else {
			query = _em.createNativeQuery(sql, SaleOrder.class).setFirstResult(
					startPosition).setMaxResults(maxResult);
		}
		query.setParameter(1, paidType);
		List<SaleOrder> orderList = query.getResultList();
		List<SaleOrder> saleOrderList = new ArrayList<SaleOrder>();
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			for (SaleOrder so : orderList) {
				if (btnStatus.equals(SaleOrder.BtnStatus_Input)) {
					so.setBtnStatus(SaleOrder.BtnStatus_Input);
					saleOrderList.add(so);
				} else if (btnStatus.equals(SaleOrder.BtnStatus_Edit)) {
					so.setBtnStatus(SaleOrder.BtnStatus_Edit);
					saleOrderList.add(so);
				} else if (btnStatus.equals(SaleOrder.BtnStatus_Cancel)) {
					so.setBtnStatus(SaleOrder.BtnStatus_Cancel);
					saleOrderList.add(so);
				} else if (btnStatus.equals(SaleOrder.BtnStatus_SaleCard)) {
					so.setBtnStatus(SaleOrder.BtnStatus_SaleCard);
					saleOrderList.add(so);
				} else if (btnStatus.equals(SaleOrder.BtnStatus_RealName)) {
					so.setBtnStatus(SaleOrder.BtnStatus_RealName);
					saleOrderList.add(so);
				} else if (btnStatus.equals(SaleOrder.BtnStatus_Sales)) {
					so.setBtnStatus(SaleOrder.BtnStatus_Sales);
					saleOrderList.add(so);
				} else if (btnStatus.equals(SaleOrder.BtnStatus_Activate)) {
					so.setBtnStatus(SaleOrder.BtnStatus_Activate);
					saleOrderList.add(so);
				} else if (btnStatus.equals(SaleOrder.BtnStatus_Freeze)) {
					if (so.getOrderStatus().equals(
							SaleOrder.SALEORDER_ORDERSTATUS_FREEZE)) {
						so.setBtnStatus(SaleOrder.BtnStatus_FreezeCancel);
					} else if (so.getOrderStatus().equals(
							SaleOrder.SALEORDER_ORDERSTATUS_ACTIVATED)) {
						so.setBtnStatus(SaleOrder.BtnStatus_Freeze);
					} else if (so.getOrderStatus().equals(
							SaleOrder.SALEORDER_ORDERSTATUS_UNDOFREEZE)) {
						so.setBtnStatus(SaleOrder.BtnStatus_Freeze);
					}
					saleOrderList.add(so);
				}
			}
			// 循环结束返回
			return saleOrderList;
		}
		return orderList;
	}

	private String getSecondSql(String orderSeq, String buyerType, String name,
			String orderStatus, BigInteger startSaleAmount,
			BigInteger endSaleAmount, String startTime, String endTime) {
		String sql = "";
		if (!PCPSUtil.isEmpty(orderSeq)) {
			sql = sql + " and t.SaleOrderSeqNo like '%" + orderSeq + "%'";
		}
		// 购卡人类型
		if (!PCPSUtil.isEmpty(buyerType)) {
			sql = sql + " and t.BuyerType = '" + buyerType + "'";
			if (buyerType.equals(SaleOrder.SaleOrder_BuyerType_Person)) {
				// 购卡人姓名
				if (!PCPSUtil.isEmpty(name)) {
					// 分个人购卡，单位购卡
					sql = sql
							+ " and t.PersonalBuyerId in (select p.personalid from PersonalCustom p where p.name like '%"
							+ name + "%')";
				}
			} else if (buyerType.equals(SaleOrder.SaleOrder_BuyerType_company)) {
				// 购卡人姓名
				if (!PCPSUtil.isEmpty(name)) {
					// 分个人购卡，单位购卡
					sql = sql
							+ " and t.OrganizeBuyerId in (select o.organizeid from OrganizeCustom o where o.fullname like '%"
							+ name + "%')";
				}
			}
		}
		// 状态
		if (!PCPSUtil.isEmpty(orderStatus)) {
			sql = sql + " and t.orderStatus='" + orderStatus + "'";
		}
		// 起始金额
		if (startSaleAmount != null
				&& startSaleAmount.compareTo(BigInteger.ZERO) > 0) {
			sql = sql + " and to_number(t.TotalSaleAmount)>=" + startSaleAmount;
		}
		// 终止金额
		if (endSaleAmount != null
				&& endSaleAmount.compareTo(BigInteger.ZERO) > 0) {
			sql = sql + " and to_number(t.TotalSaleAmount)<=" + endSaleAmount;
		}
		// 起始时间
		if (!PCPSUtil.isEmpty(startTime)) {
			sql = sql + " and t.SaleTime>=to_date('" + startTime
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
		}
		// 终止时间
		if (!PCPSUtil.isEmpty(endTime)) {
			sql = sql + " and t.SaleTime<= to_date('" + endTime
					+ " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
		}
		return sql;
	}

	// 前台传入菜单判断状态
	private String getSql(String btnStatus, String sql) {
		if (btnStatus.equals(SaleOrder.BtnStatus_Input)
				|| btnStatus.equals(SaleOrder.BtnStatus_Edit)
				|| btnStatus.equals(SaleOrder.BtnStatus_SaleCard)
				|| btnStatus.equals(SaleOrder.BtnStatus_RealName)
				|| btnStatus.equals(SaleOrder.BtnStatus_Sales)) {
			sql = sql + " and t.OrderStatus ='"
					+ SaleOrder.SALEORDER_ORDERSTATUS_INPUT + "'";
		} else if (btnStatus.equals(SaleOrder.BtnStatus_Freeze)) {
			sql = sql + " and t.OrderStatus in('"
					+ SaleOrder.SALEORDER_ORDERSTATUS_ACTIVATED + "','"
					+ SaleOrder.SALEORDER_ORDERSTATUS_FREEZE + "','"
					+ SaleOrder.SALEORDER_ORDERSTATUS_UNDOFREEZE + "')";
		}
		return sql;
	}

	@Override
	public BigInteger getAllTotalSaleAmountBySaleOrderIdArray(
			BigInteger[] saleOrderIds) {
		String str = "";
		for (BigInteger b : saleOrderIds) {
			str += b + ",";
		}
		str = str.substring(0, str.length() - 1);
		String sql = "select sum(s.TotalSaleAmount) from SaleOrder s where s.SaleOrderId in ("
				+ str + ")";
		Query query = _em.createNativeQuery(sql);
		return new BigInteger(query.getSingleResult().toString());
	}

	@SuppressWarnings("unchecked")
	@Override
	public HashMap<Integer, String> getSaleBranchNoListBySaleOrderIds(
			BigInteger[] saleOrderIds) {
		String str = "";
		for (BigInteger b : saleOrderIds) {
			str += b + ",";
		}
		str = str.substring(0, str.length() - 1);
		String sql = "select distinct(s.SaleBranchNo) from SaleOrder s where s.SaleOrderId in ("
				+ str + ")";
		HashMap<Integer, String> hm = new HashMap<Integer, String>();
		Query query = _em.createNativeQuery(sql);
		List<Object> list = query.getResultList();
		int i = 0;
		for (Object ob : list) {
			hm.put(i, ob.toString());
			i++;
		}
		return hm;
	}

	// 根据激活单编号查询销售单列表
	@SuppressWarnings("unchecked")
	@Override
	public List<SaleOrder> getSaleOrderListByActiveOrderId(
			BigInteger activeOrderId, String btnStatus) {
		String sql = "select s.* from SaleOrder s, ActiveOrderDetail a where s.saleorderid=a.saleorderid and a.activeorderid="
				+ activeOrderId;
		if (!PCPSUtil.isEmpty(btnStatus)) {
			if (btnStatus.equals(SaleOrder.BtnStatus_ActivateConfirm)) {
				sql = sql + " and s.orderstatus in('"
						+ SaleOrder.SALEORDER_ORDERSTATUS_LIBRARY + "','"
						+ SaleOrder.SALEORDER_ORDERSTATUS_ACTIVATED + "')";
			} else if (btnStatus
					.equals(SaleOrder.BtnStatus_ActivateCancelOrder)) {
				sql = sql + " and s.orderstatus in('"
						+ SaleOrder.SALEORDER_ORDERSTATUS_ACTIVATED + "','"
						+ SaleOrder.SALEORDER_ORDERSTATUS_UNDOFREEZE + "')";
			}
		}
		sql = sql + "order by SaleTime asc";
		Query query = _em.createNativeQuery(sql, SaleOrder.class);
		List<SaleOrder> orderList = query.getResultList();
		List<SaleOrder> saleOrderList = new ArrayList<SaleOrder>();
		// 前台传入菜单操作
		if (!PCPSUtil.isEmpty(btnStatus)) {
			for (SaleOrder so : orderList) {
				if (btnStatus.equals(SaleOrder.BtnStatus_ActivateConfirm)) {
					if (so.getOrderStatus().equals(
							SaleOrder.SALEORDER_ORDERSTATUS_LIBRARY)) {
						so.setBtnStatus(SaleOrder.BtnStatus_ActivateConfirm);
					} else if (so.getOrderStatus().equals(
							SaleOrder.SALEORDER_ORDERSTATUS_ACTIVATED)) {
						so
								.setBtnStatus(SaleOrder.BtnStatus_ActivateConfirmHidden);
					}
					saleOrderList.add(so);
				} else if (btnStatus
						.equals(SaleOrder.BtnStatus_ActivateCancelOrder)) {
					if (so.getOrderStatus().equals(
							SaleOrder.SALEORDER_ORDERSTATUS_ACTIVATED)) {
						so
								.setBtnStatus(SaleOrder.BtnStatus_ActivateCancelOrder);
					} else if (so.getOrderStatus().equals(
							SaleOrder.SALEORDER_ORDERSTATUS_UNDOFREEZE)) {
						so
								.setBtnStatus(SaleOrder.BtnStatus_ActivateCancelOrder);
					}
					saleOrderList.add(so);
				}
			}
			return saleOrderList;
		}
		return orderList;
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleOrder> getActivateSaleOrderList(PCPSWebInput webInput,
			String orderSeqNo, BigInteger startSaleAmount,
			BigInteger endSaleAmount, String startTime, String endTime,
			String saleBranchNo, String activateBranchNo, String orderStatus,
			String paidType,String feeType) {
		// 平台管理员无法查询
		String sql = "select t.* from SaleOrder t where (?2 is null or PaidType=?2)";
		if (!PCPSUtil.isEmpty(feeType)) {
			sql += " and saleorderid in(select saleorderid from SaleOrderPaidDetail t where paidtype='"
					+ feeType
					+ "' and TranTime>=to_date('"
					+ startTime
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss') and TranTime<= to_date('"
					+ endTime + " 23:59:59','yyyy-mm-dd hh24:mi:ss'))";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		// 查询条件
		sql += getSecondSql(orderSeqNo, null, null, null, startSaleAmount,
				endSaleAmount, startTime, endTime);
		if (PCPSUtil.isEmpty(activateBranchNo)) {
			sql += " and orderStatus=?1 and ActivateBranchNo is null order by SaleTime asc";
		} else {
			sql += " and orderStatus=?1 and ActivateBranchNo ='"
					+ activateBranchNo + "' order by SaleTime asc";
		}
		Query query = _em.createNativeQuery(sql, SaleOrder.class);
		query.setParameter(1, orderStatus);
		query.setParameter(2, paidType);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleOrder> getSaleOrderListByVoucherNo(String voucherNo) {
		// 平台管理员无法查询
		String sql = "select t.* from SaleOrder t where CommitVoucherNo=?1";
		Query query = _em.createNativeQuery(sql, SaleOrder.class);
		query.setParameter(1, voucherNo);
		return query.getResultList();
	}

	/**
	 * 根据条件检索销售单信息列表--报表（西邮寄卡实名制信息统计表）
	 * 
	 * @param saleBranchNo
	 *            销售机构
	 * @param buyerType
	 *            购卡人类型
	 * @return
	 */
	Map<BigInteger, String> cardclassmap = new HashMap<BigInteger, String>();// 卡种类
	Map<BigInteger, BankAccount> bankacc = new HashMap<BigInteger, BankAccount>();// 银行账户

	@SuppressWarnings("unchecked")
	public List<Object[]> getSaleOrderByRealName(PCPSWebInput webInput,
			String saleBranchNo, Date sratDate, Date endDate) {
		if (webInput == null || PCPSUtil.isEmpty(saleBranchNo)
				|| sratDate == null || endDate == null)
			return null;
		String sql = "select distinct(a.saleorderid), a.saletime,b.salebranchname,a.SaleOrderSeqNo,a.PersonalBuyerId,a.OrganizeBuyerId,a.ProxyBuyerId,a.buyertype,a.remark "
				+ " from saleorder a,SaleBranch b,SaleOrderRealNameDetail c"
				+ " where a.salebranchno = b.salebranchno"
				+ " and a.saleorderid=c.saleorderid"
				+ " and a.TranDate>=?1"
				+ " and a.TranDate<=?2"
				+ " and a.salebranchno in("
				+ saleBranchNo + ")";

		sql = sql + " order by a.saletime,a.saleorderid desc";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, sratDate);
		query.setParameter(2, endDate);
		List<Object[]> saleorder_list = query.getResultList();// 得到销售单信息
		if (saleorder_list == null && saleorder_list.size() <= 0)// 如果为空返回
			return null;
		List<Object[]> realnameList = new ArrayList<Object[]>();// 定义返回数组
		Object[] str = null;
		String CardClassicName = "";// 预付卡类型
		String Cardvalue = "";// 卡面值
		String CardSeqNo = "";// 卡序号段
		String CardCount = "";// 张数
		String OrderLineTotal = "";// 小计
		String PaidType = "";// 付款形式
		String AccountName = "";// 账户名称
		String BankName = "";// 开户名称
		String AccountNo = "";// 号
		BigInteger Total = new BigInteger("0");// 总计
		for (int i = 0; i < saleorder_list.size(); i++) {// 循环
			str = new Object[18];// 定义数组
			Object[] obj = saleorder_list.get(i);

			CardClassicName = "";// 预付卡类型
			Cardvalue = "";// 卡面值
			CardSeqNo = "";// 卡序号段
			CardCount = "";// 张数
			OrderLineTotal = "";// 小计
			PaidType = "";// 付款形式
			Total = new BigInteger("0");// 总计
			AccountName = "";// 账户名称
			BankName = "";// 开户名称
			AccountNo = "";// 号

			String saleorderid = obj[0].toString();
			str[0] = PCPSUtil.getStringYMDByDate(PCPSUtil
					.getDateYMDByString(obj[1].toString()));// 销售日期
			str[1] = obj[2];// 销售单位
			str[2] = obj[3];// 购卡单号
			str[3] = obj[4];// 个人id
			str[4] = obj[5];// 单位id
			str[5] = obj[6];// 代理人
			str[6] = obj[7];// 购卡人类型
			str[7] = obj[8];// 备注
			List<SaleOrderCardDetail> saleordercardlist = saleOrderCardDetailDaoImpl
					.getSaleOrderCardDetailListBySaleOrderId(new BigInteger(
							saleorderid));
			if (saleordercardlist == null || saleordercardlist.size() <= 0) {
				realnameList.add(str);
				continue;
			}

			SaleOrderCardDetail carddatil = null;
			for (int i_1 = 0; i_1 < saleordercardlist.size(); i_1++) {
				carddatil = saleordercardlist.get(i_1);
				if (carddatil != null) {
					if (PCPSUtil.isEmpty(CardClassicName))// 卡类型
						CardClassicName = getCardCalessName(carddatil
								.getCardClassicId());
					else
						CardClassicName = CardClassicName
								+ ","
								+ getCardCalessName(carddatil
										.getCardClassicId());

					if (PCPSUtil.isEmpty(Cardvalue))// 卡面值
						Cardvalue = String
								.valueOf(carddatil.getInitDeposit() / 100);
					else
						Cardvalue = Cardvalue
								+ ","
								+ String
										.valueOf(carddatil.getInitDeposit() / 100);

					if (PCPSUtil.isEmpty(CardSeqNo)) {// 卡号段
						CardSeqNo = "[" + carddatil.getStartCardSeqNo() + "--"
								+ carddatil.getEndCardSeqNo() + "]";
					} else {
						CardSeqNo = CardSeqNo + "," + "["
								+ carddatil.getStartCardSeqNo() + "--"
								+ carddatil.getEndCardSeqNo() + "]";
					}

					if (PCPSUtil.isEmpty(CardCount))// 数量
						CardCount = carddatil.getCardCount().toString();
					else
						CardCount = CardCount + ","
								+ carddatil.getCardCount().toString();

					if (PCPSUtil.isEmpty(OrderLineTotal))// 小计
						OrderLineTotal = String.valueOf(Double
								.parseDouble(carddatil.getOrderLineTotal()
										.toString()) / 100);
					else
						OrderLineTotal = OrderLineTotal
								+ ","
								+ String.valueOf(Double.parseDouble(carddatil
										.getOrderLineTotal().toString()) / 100);

					Total = Total.add(carddatil.getOrderLineTotal());
				}
			}
			str[8] = CardClassicName;
			str[9] = Cardvalue;
			str[10] = CardSeqNo;
			str[11] = CardCount;
			str[12] = OrderLineTotal;
			str[13] = Total;

			List<SaleOrderPaidDetail> paidlist = saleOrderPaidDetailDaoImpl
					.getSaleOrderPaidDetailListBySaleOrderId(new BigInteger(
							saleorderid));
			if (paidlist == null || paidlist.size() <= 0) {
				realnameList.add(str);
				continue;
			}
			for (int i_2 = 0; i_2 < paidlist.size(); i_2++) {
				SaleOrderPaidDetail paiddatil = paidlist.get(i_2);
				if (paiddatil.getPaidAccountId() == null) {
					if (PCPSUtil.isEmpty(PaidType)) {
						PaidType = "现金";
					} else
						PaidType = PaidType + "/现金";
				} else {
					if (PCPSUtil.isEmpty(PaidType)) {
						PaidType = "转账";
					} else
						PaidType = PaidType + "/转账";

					BankAccount bankAccount = getBankAccount(paiddatil
							.getPaidAccountId());
					if (bankAccount != null) {
						if (PCPSUtil.isEmpty(AccountName)) {
							AccountName = "[" + bankAccount.getAccountName()
									+ "]";
							BankName = "[" + bankAccount.getBankName() + "]";
							AccountNo = "[" + bankAccount.getAccountNo() + "]";
						} else {
							AccountName = AccountName + "," + "["
									+ bankAccount.getAccountName() + "]";
							BankName = BankName + "," + "["
									+ bankAccount.getBankName() + "]";
							AccountNo = AccountNo + "," + "["
									+ bankAccount.getAccountNo() + "]";
						}
					}
				}
			}
			str[14] = AccountName;
			str[15] = BankName;
			str[16] = AccountNo;
			str[17] = PaidType;
			realnameList.add(str);
		}
		return realnameList;
	}

	/**
	 * 根据卡种类id检索卡名称
	 */
	private String getCardCalessName(BigInteger cardClassicId) {
		if (cardclassmap.get(cardClassicId) == null) {
			CardClassic cardClassic = cardClassicDaoImpl
					.getCardClassicByCardClassicId(cardClassicId);
			if (cardClassic != null) {
				cardclassmap.put(cardClassic.getCardClassicId(), cardClassic
						.getCardClassicName());
				return cardClassic.getCardClassicName();
			} else
				return null;
		} else {
			return cardclassmap.get(cardClassicId).toString();
		}
	}

	/**
	 * 根据银行账户id检索银行账户信息
	 */
	private BankAccount getBankAccount(BigInteger PaidAccountId) {
		if (bankacc.get(PaidAccountId) == null) {
			BankAccount bankAccount = bankAccountDaoImpl
					.getBankAccountByBankAccountId(PaidAccountId);
			if (bankAccount != null) {
				bankacc.put(PaidAccountId, bankAccount);
				return bankAccount;
			} else
				return null;
		} else
			return (BankAccount) bankacc.get(PaidAccountId);
	}

	/**
	 * -------------------------同一经办人下使用不同的单位名称进行购买卡Start-------------------------
	 */
	/**
	 * 风险预警数据查询 查询当天销售单中为单位购卡的所有代理人列表
	 * 
	 * @param date
	 * @return
	 */
	@Override
	@SuppressWarnings("unchecked")
	public List<Object> getProxybuyerListForAlarm(String date) {
		String sql1 = "select o.proxybuyerid from saleorder o where o.proxybuyerid is not null and o.buyertype = '"
				+ SaleOrder.SaleOrder_BuyerType_company
				+ "' and o.organizebuyerid is not null and (saletime >= to_date('"
				+ date
				+ " 00:00:00', 'yyyy-MM-dd hh24:mi:ss') and saletime <= to_date('"
				+ date
				+ " 23:59:59', 'yyyy-MM-dd hh24:mi:ss')) group by o.proxybuyerid";
		Query query = _em.createNativeQuery(sql1);
		List<Object> proxybuyerList = query.getResultList();
		return proxybuyerList;
	}

	/**
	 * 代理人购卡个数
	 * 
	 * @param proxyBuyer
	 *            购卡代理人
	 * @return
	 */
	@Override
	public Integer getProxybuyerSaleOrderNum(BigInteger proxyBuyer) {
		String sql2 = "select nvl(count(*),0) from (select o.organizebuyerid from saleorder o where o.buyertype = '"
				+ SaleOrder.SaleOrder_BuyerType_company
				+ "' and o.organizebuyerid is not null and o.proxybuyerid is not null and o.proxybuyerid = ?1)";
		Query query = _em.createNativeQuery(sql2);
		query.setParameter(1, proxyBuyer);
		return Integer.parseInt(query.getSingleResult().toString());
	}

	/**
	 * 查询可疑销售单信息
	 * 
	 * @param proxyBuyer
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<SaleOrder> getProxyBuyerSaleOrderList(BigInteger proxyBuyer) {
		String sql3 = "select o.* from saleorder o where o.buyertype = '"
				+ SaleOrder.SaleOrder_BuyerType_company
				+ "' and o.organizebuyerid is not null and o.proxybuyerid = ?1 order by o.saletime desc";
		Query query = _em.createNativeQuery(sql3, SaleOrder.class);
		query.setParameter(1, proxyBuyer);
		return query.getResultList();
	}

	/**
	 * -------------------------同一经办人下使用不同的单位名称进行购买卡end-------------------------
	 */
	/**
	 * 同一购卡人使用转账方式购卡，转账账户名称与购卡人名称不符的
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getSaleOrderForAlarm(Date date, String issuecompanyno) {
		String sql = "select distinct(a.saleorderid),a.personalbuyerid,a.organizebuyerid,s.paidaccountid"
				+ " from saleorder a,saleorderpaiddetail s"
				+ " where a.saleorderid = s.saleorderid"
				+ " and s.paidtype<>'0'"
				+ " and a.trandate=?1  and s.PaidAccountId is not null"
				+ " and SaleBranchNo in (select t.subbranchno from sysbranchsubord t where t.subbranchtype = '12' and t.branchno = ?2)"
				+ " order by a.saleorderid ";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, date);
		query.setParameter(2, issuecompanyno);
		return query.getResultList();
	}

	/**
	 * 同一经办人在短时期内进行多次购买的未超过限额
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getSaleOrderMoreTimes(String issuecompanyno,
			Date startTime, Date endTime, double amount, Integer num) {
		String sql = " select PersonalBuyerId,OrganizeBuyerId,count(PersonalBuyerId),sum(TotalSaleAmount) "
				+ " from SaleOrder where PersonalBuyerId is not null "
				+ " and SaleBranchNo in (select t.subbranchno from sysbranchsubord t where t.subbranchtype = '12' and t.branchno = ?1)"
				+ " and TranDate>=?2 and TranDate <=?3"
				+ " group by PersonalBuyerId,OrganizeBuyerId  having count(PersonalBuyerId)>?4  and  sum(TotalSaleAmount)>?5"
				+ " union all "
				+ " select PersonalBuyerId,OrganizeBuyerId,count(OrganizeBuyerId),sum(TotalSaleAmount)"
				+ " from SaleOrder where OrganizeBuyerId is not null "
				+ " and SaleBranchNo in (select t.subbranchno from sysbranchsubord t where t.subbranchtype = '12' and t.branchno = ?1)"
				+ " and TranDate>=?2 and TranDate <=?3"
				+ " group by PersonalBuyerId,OrganizeBuyerId  having count(OrganizeBuyerId)>?4  and  sum(TotalSaleAmount)>?5";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, issuecompanyno);
		query.setParameter(2, startTime);
		query.setParameter(3, endTime);
		query.setParameter(4, num);
		query.setParameter(5, amount);
		List<Object[]> list = query.getResultList();
		return list;
	}

	/**
	 * 根据时间段，购卡人主题，发卡商检索销售单清单
	 */
	@SuppressWarnings("unchecked")
	public List<SaleOrder> getSaleOrderByDateAndPer(String issueCompanyNo,
			Date startTime, Date endTime, String BuyerId) {
		if (PCPSUtil.isEmpty(issueCompanyNo) || startTime == null
				|| endTime == null || PCPSUtil.isEmpty(BuyerId))
			return null;
		String sql = " select * from SaleOrder where "
				+ " SaleBranchNo in (select t.subbranchno from sysbranchsubord t where t.subbranchtype = '12' and t.branchno = ?1)"
				+ " and (PersonalBuyerId =?2 or OrganizeBuyerId=?2)"
				+ " and TranDate>=?3 and TranDate <=?4" + " order by TranDate";
		Query query = _em.createNativeQuery(sql, SaleOrder.class);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, BuyerId);
		query.setParameter(3, startTime);
		query.setParameter(4, endTime);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleOrder> getSaleOrderByAlarmRule3(String issueCompanyNo,
			String startTime, String endTime) {
		String sql = "select * from SaleOrder a  "
				+ " where substr(a.salebranchno,0,6) = ?1 "
				+ " and a.trandate >= to_date(?2,'yyyy-mm-dd') "
				+ " and a.trandate <= to_date(?3,'yyyy-mm-dd') "
				+ " and a.orderstatus <> ?4 " + " order by a.saleorderid asc ";
		Query query = _em.createNativeQuery(sql, SaleOrder.class);
		query.setParameter(1, issueCompanyNo);
		query.setParameter(2, startTime);
		query.setParameter(3, endTime);
		query.setParameter(4, SaleOrder.SALEORDER_ORDERSTATUS_CANCEL);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<Object[]> getSaleOrderListReportByCond(PCPSWebInput webInput,
			String branchNo, String startDate, String endDate) {
		String sqlcond = PCPSUtil
				.getSqlCond(webInput, branchNo, "SaleBranchNo");
		sqlcond = sqlcond.replace("SaleBranchNo", "t.SaleBranchNo");
		String sql = "select t.saleorderid,t.saleorderseqno,b.salebranchname,to_char(t.saletime,'yyyy-mm-dd hh24:mi:ss') saletime,t.orderstatus,t.remark "
				+ "from saleorder t,salebranch b where t.salebranchno=b.salebranchno "
				+ sqlcond
				+ " and t.LastActiveDate >= to_date(?1,'yyyy-mm-dd') and t.LastActiveDate <= to_date(?2,'yyyy-mm-dd') and t.OrderStatus='"
				+ SaleOrder.SALEORDER_ORDERSTATUS_ACTIVATED
				+ "' order by t.salebranchno,t.saletime,t.orderstatus";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	public List<Object[]> getSaleOrderCountReportByCond(PCPSWebInput webInput,
			String branchNo, String startDate, String endDate) {
		String sqlcond = PCPSUtil.getSqlCond(webInput, branchNo,
				"t.SaleBranchNo");
		String sql = "select sb.salebranchname,to_char(a.saletime, 'yyyy-mm-dd hh24:mi:ss') saletime,cc.cardclassicname,"
				+ "(select case when m.mediatype= '0' then '磁卡' when m.mediatype= '1' then 'CPU多界面卡' end as mediatype "
				+ " from makecardcontrol m,cardface c "
				+ " where m.cardfaceid=c.cardfaceid and m.batchno=a.batchno)"
				+ " mediatype,"
				+ " a.TotalCardCount,a.TotalSaleAmount "
				+ "from ("
				+ " select t.salebranchno,s.cardclassicid,s.batchno,t.saletime,sum(t.TotalCardCount) TotalCardCount,sum(t.TotalSaleAmount) TotalSaleAmount "
				+ " from SaleOrderCardDetail s,saleorder t "
				+ " where s.saleorderid=t.saleorderid(+) and t.OrderStatus='"
				+ SaleOrder.SALEORDER_ORDERSTATUS_ACTIVATED
				+ "' "
				+ sqlcond
				+ " and t.LastActiveDate >= to_date(?1,'yyyy-mm-dd')"
				+ " and t.LastActiveDate <= to_date(?2,'yyyy-mm-dd')"
				+ " group by t.salebranchno,s.cardclassicid,s.batchno,t.saletime "
				+ ")a,salebranch sb,cardclassic cc"
				+ " where a.salebranchno=sb.salebranchno and a.cardclassicid=cc.cardclassicid "
				+ "order by a.salebranchno,a.cardclassicid,a.batchno";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public List<SaleOrder> getSaleOrderCreditPayListByCondition(
			PCPSWebInput webInput, String orderSeqNo, String startTime,
			String endTime, String saleBranchNo, Integer page) {
		if (null == webInput) {
			return null;
		}
		int maxResult = Config.PAGE_SIZE;
		int startPosition = maxResult * page;
		String sqlcond = PCPSUtil.getSqlCond(webInput, saleBranchNo,
				"t.salebranchno");
		String sql = "select t.* from SaleOrder t"
				+ " where (?1 is null or t.saleorderseqno = ?1)"
				+ " and t.saletime >= to_date(?2,'yyyy-MM-dd hh24:mi:ss')"
				+ " and t.saletime <= to_date(?3,'yyyy-MM-dd hh24:mi:ss')"
				+ " and t.paidtype = '1' " + sqlcond
				+ " and t.OrderStatus !='2' "
				// + " and (t.totalsaleamount+t.factfee)>t.alreaypaidamount"
				+ " order by t.saletime desc";
		Query query = _em.createNativeQuery(sql, SaleOrder.class)
				.setFirstResult(startPosition).setMaxResults(maxResult);
		query.setParameter(1, orderSeqNo);
		query.setParameter(2, startTime + " 00:00:00");
		query.setParameter(3, endTime + " 23:59:59");
		return query.getResultList();
	}

	@Override
	public BigInteger getSaleOrderCreditPayTotalByCondition(
			PCPSWebInput webInput, String orderSeqNo, String startTime,
			String endTime, String saleBranchNo) {
		if (null == webInput) {
			return null;
		}
		String sqlcond = PCPSUtil.getSqlCond(webInput, saleBranchNo,
				"t.salebranchno");
		String sql = "select nvl(count(*),0) from SaleOrder t"
				+ " where (?1 is null or t.saleorderseqno = ?1)"
				+ " and t.saletime >= to_date(?2,'yyyy-MM-dd hh24:mi:ss')"
				+ " and t.saletime <= to_date(?3,'yyyy-MM-dd hh24:mi:ss')"
				+ " and t.paidtype = '1' " + sqlcond
				+ " and t.OrderStatus !='2' ";
		// + " and (t.totalsaleamount+t.factfee)>t.alreaypaidamount";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, orderSeqNo);
		query.setParameter(2, startTime + " 00:00:00");
		query.setParameter(3, endTime + " 23:59:59");
		return new BigInteger(query.getSingleResult().toString());
	}

	// select (select salebranchname from salebranch where
	// salebranchno=a.salebranchno) salebranchname,(select salesname from sales
	// where salesid=t.salesid)
	// salesname,a.saleorderseqno,cardclassicname,cardcount,initdeposit,orderlinetotal,orderlinetotal
	// from
	// (select
	// c.saleorderid,s.saleorderseqno,cc.cardclassicname,c.cardcount,c.initdeposit/100
	// initdeposit,c.orderlinetotal/100 orderlinetotal,s.salebranchno from
	// saleordercarddetail c,cardclassic cc,saleorder s where
	// c.saleorderid in (select saleorderid from saleorder t where
	// t.salebranchno like '909090%' and
	// t.trandate between to_date('2013-07-07','yyyy-mm-dd') and
	// to_date('2013-07-08','yyyy-mm-dd') and t.salebranchno='9090909861042001')
	// and c.cardclassicid=cc.cardclassicid and c.saleorderid=s.saleorderid) a
	// left join saleordersalesdetail t on a.saleorderid=t.saleorderid order by
	// salebranchname,t.salesid,cardclassicname,t.saleorderid
	/**
	 * 营销员业务统计表
	 * 
	 * @param input
	 * @param branchNo
	 * @param startDate
	 * @param endDate
	 * @return
	 */
	@SuppressWarnings("unchecked")
	public List<Object[]> getSalesFromSaleOrddr(PCPSWebInput input,
			String branchNo, String startDate, String endDate) {
		if (null == input) {
			return null;
		}
		String sqlcond = PCPSUtil.getSqlCond(input, branchNo, "t.salebranchno");
		String sql = "select (select salebranchname from salebranch where salebranchno=a.salebranchno) salebranchname,(select salesname from sales where salesid=t.salesid) salesname,a.saleorderseqno,cardclassicname,cardcount,initdeposit,orderlinetotal from "
				+ "(select c.saleorderid,s.saleorderseqno,cc.cardclassicname,c.cardcount,c.initdeposit initdeposit,c.orderlinetotal orderlinetotal,s.salebranchno from saleordercarddetail c,cardclassic cc,saleorder s where "
				+ "c.saleorderid in (select saleorderid from saleorder t where "
				+ "t.trandate between to_date('"
				+ startDate
				+ "','yyyy-mm-dd') and to_date('"
				+ endDate
				+ "','yyyy-mm-dd') "
				+ sqlcond
				+ " ) and c.cardclassicid=cc.cardclassicid and c.saleorderid=s.saleorderid) a "
				+ "left join saleordersalesdetail t on a.saleorderid=t.saleorderid order by salebranchname,t.salesid,cardclassicname,t.saleorderid";
		Query query = _em.createNativeQuery(sql);
		return query.getResultList();
	}

	@SuppressWarnings("unchecked")
	@Override
	public SaleOrder getSaleOrderByCardSeqNo(PCPSWebInput webInput,
			String cardSeqNo) {
		if (null == webInput) {
			return null;
		}
		String sql = "select s.saleorderseqno from saleordercarddetail t,saleorder s"
				+ " where ?1 between t.startcardseqno and t.endcardseqno"
				+ " and t.saleorderid = s.saleorderid";
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, cardSeqNo);
		List<String> list = query.getResultList();
		if (null == list || list.size() <= 0) {
			return null;
		}
		String saleOrderSeqNo = list.get(0).toString();
		return getSaleOrderBySaleOrderSeqNo(saleOrderSeqNo);
	}

	@Override
	public Object[] getSaleOrderListByConditionSum(PCPSWebInput webInput,
			String orderSeqNo, String buyerType, String name,
			String orderStatus, BigInteger startSaleAmount,
			BigInteger endSaleAmount, String startTime, String endTime,
			String saleBranchNo, String btnStatus, String paidType,String feeType) {
		// 平台管理员无法查询
		String sql = "select nvl(sum(TotalSaleAmount),0),nvl(sum(FactFee),0),nvl(sum(AlreayPaidAmount),0),"
				+ "nvl(sum(TotalCardCount),0) from SaleOrder t where (?1 is null or paidType=?1)";
		if (!PCPSUtil.isEmpty(feeType)) {
			sql += " and saleorderid in(select saleorderid from SaleOrderPaidDetail t where paidtype='"
					+ feeType
					+ "' and TranTime>=to_date('"
					+ startTime
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss') and TranTime<= to_date('"
					+ endTime + " 23:59:59','yyyy-mm-dd hh24:mi:ss'))";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		if (!PCPSUtil.isEmpty(orderSeqNo)) {
			sql = sql + " and t.SaleOrderSeqNo like '%" + orderSeqNo + "%'";
		}
		// 购卡人类型
		if (!PCPSUtil.isEmpty(buyerType)) {
			sql = sql + " and t.BuyerType = '" + buyerType + "'";
			if (buyerType.equals(SaleOrder.SaleOrder_BuyerType_Person)) {
				// 购卡人姓名
				if (!PCPSUtil.isEmpty(name)) {
					// 分个人购卡，单位购卡
					sql = sql
							+ " and t.PersonalBuyerId in (select p.personalid from PersonalCustom p where p.name like '%"
							+ name + "%')";
				}
			} else if (buyerType.equals(SaleOrder.SaleOrder_BuyerType_company)) {
				// 购卡人姓名
				if (!PCPSUtil.isEmpty(name)) {
					// 分个人购卡，单位购卡
					sql = sql
							+ " and t.OrganizeBuyerId in (select o.organizeid from OrganizeCustom o where o.fullname like '%"
							+ name + "%')";
				}
			}
		}
		// 状态
		if (!SaleOrder.SALEORDER_ORDERSTATUS_CANCEL.equals(orderStatus)) {
			sql = sql + " and t.orderStatus<>'"
					+ SaleOrder.SALEORDER_ORDERSTATUS_CANCEL + "'";
		}
		if (!PCPSUtil.isEmpty(orderStatus)) {
			sql = sql + " and t.orderStatus='" + orderStatus + "'";
		}
		// 起始金额
		if (startSaleAmount != null
				&& startSaleAmount.compareTo(BigInteger.ZERO) > 0) {
			sql = sql + " and to_number(t.TotalSaleAmount)>=" + startSaleAmount;
		}
		// 终止金额
		if (endSaleAmount != null
				&& endSaleAmount.compareTo(BigInteger.ZERO) > 0) {
			sql = sql + " and to_number(t.TotalSaleAmount)<=" + endSaleAmount;
		}
		// 起始时间
		if (!PCPSUtil.isEmpty(startTime)) {
			sql = sql + " and t.SaleTime>=to_date('" + startTime
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss')";
		}
		// 终止时间
		if (!PCPSUtil.isEmpty(endTime)) {
			sql = sql + " and t.SaleTime<= to_date('" + endTime
					+ " 23:59:59','yyyy-mm-dd hh24:mi:ss')";
		}
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, paidType);
		Object[] obj = (Object[]) query.getSingleResult();
		return obj;
	}

	@Override
	public Object[] getActivateSaleOrderListSum(PCPSWebInput webInput,
			String orderSeqNo, BigInteger startSaleAmount,
			BigInteger endSaleAmount, String startTime, String endTime,
			String saleBranchNo, String activateBranchNo, String orderStatus,
			String paidType,String feeType) {
		// 平台管理员无法查询
		String sql = "select sum(TotalSaleAmount),sum(FactFee),sum(AlreayPaidAmount),"
				+ "sum(TotalCardCount) from SaleOrder t where (?2 is null or PaidType=?2)";
		if (!PCPSUtil.isEmpty(feeType)) {
			sql += " and saleorderid in(select saleorderid from SaleOrderPaidDetail t where paidtype='"
					+ feeType
					+ "' and TranTime>=to_date('"
					+ startTime
					+ " 00:00:00','yyyy-mm-dd hh24:mi:ss') and TranTime<= to_date('"
					+ endTime + " 23:59:59','yyyy-mm-dd hh24:mi:ss'))";
		}
		// 判断是查询全部还是选定机构
		sql += PCPSUtil.getSqlCond(webInput, saleBranchNo, "SaleBranchNo");
		// 查询条件
		sql += getSecondSql(orderSeqNo, null, null, null, startSaleAmount,
				endSaleAmount, startTime, endTime);
		if (PCPSUtil.isEmpty(activateBranchNo)) {
			sql += " and orderStatus=?1 and ActivateBranchNo is null ";
		} else {
			sql += " and orderStatus=?1 and ActivateBranchNo ='"
					+ activateBranchNo + "' ";
		}
		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, orderStatus);
		query.setParameter(2, paidType);
		Object[] obj = (Object[]) query.getSingleResult();
		return obj;
	}

	@Override
	public BigInteger getSaleOrderFactFeeBySaleBrchNos(String saleBrchNos,
			String startDate, String endDate) {
		String sql = "select nvl(sum(t.factFee),0) from saleorder t"
				+ " where t.salebranchno in (" + saleBrchNos + ")"
				+ " and t.trandate >=to_date(?1,'yyyy-MM-dd')"
				+ " and t.trandate <=to_date(?2,'yyyy-MM-dd')"
				+ " and t.OrderStatus <> 'C'";

		Query query = _em.createNativeQuery(sql);
		query.setParameter(1, startDate);
		query.setParameter(2, endDate);
		return new BigInteger(query.getSingleResult().toString());
	}

}